<?php
/**
* @filename source_class.php 2013-11-11 02:10:56x
* @author Huming Xu <huming17@126.com>
* @version 1.0.0
* @copyright DZF (c) 2013, Huming Xu
*/
if(!defined('IN_SITE')) {
	exit('Access Denied');
}
class course_main{
	public $info_array = array();
	public $page_array = array();
	public $tree_array = array();

	static function &instance() {
		static $object;
		if(empty($object)) {
			$object = new self();
		}
		return $object;
	}
	/*
	*	获取一条信息
	*/
	public function one_info($table_name,$key_name,$key_id='') {
		$info = array();
		if($table_name && $key_name && $key_id){
			$sql = "SELECT * FROM ".DB::table($table_name)." WHERE ".$key_name."='".$key_id."' LIMIT 1";
			$info = DB::fetch_first($sql);	
		}
		return $info;
	}
	
	/*
	 *	根据当前用户获取学校id
	 */
	public function one_sm_info($table_name,$key_name,$key_id='') {
		$info = array();
		$sm_id='';
		if($table_name && $key_name && $key_id){
			$sql = "SELECT * FROM ".DB::table($table_name)." WHERE ".$key_name."='".$key_id."' LIMIT 1";
			$info = DB::fetch_first($sql);
			$sm_id=$info['sm_id']; 
		}
		return $sm_id;
	}
	/*
	 *	获取上课信息
	 */
	public function study_info($name_arr=array(),$condition=array()) {		
		$info = array();
		$select=$am='';
		$id1=$condition['c_key1'];		
		$flag=$condition['flag'];
		$whereSql=$condition['whereSql'];
		if($id1 ){
			if($flag==1){
				$select='ud.am_id_1';
				$am='ue_area_id3';
			}elseif($flag==2){
				$select='ud.am_id_2';
				$am='ue_area_id2';
			}elseif($flag==3){
				$select='ud.am_id_3';
				$am='ue_area_id3';
			}elseif($flag==4){
				$select='ud.sm_id';
			}elseif($flag==5){
				$select='ud.class_id';
			}elseif($flag==6){
				$select='tc.user_id';
			}
			$left = " LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id";
			if($condition['cm']==1){
				$left .= " LEFT JOIN ".DB::table('course_main')." cm ON cm.cm_id=tc.cm_id";
			}
			$sql ="select DISTINCT(".$select.") from ".DB::table('trade_course')." tc ".$left." 						
						where tc.isdelete = 0 and tc.tc_flag =2  and ud.isdelete = 0
						and tc.cm_id = ".$condition['cm_id'].$whereSql;			
			$info = DB::fetch_all($sql);
		}			
		if($info){			
			foreach ($info as $k => $v){				
				if($v[$id1]){				
					$info[$k]['cm_name'] = $name_arr['cm_name'];//课程名称
					$info[$k]['ch_name'] = $name_arr['ch_name'];//课时名称
					$info[$k]['am_1_name'] = $name_arr['am_1_name'];//省名称
					$info[$k]['am_2_name'] = $name_arr['am_2_name'];//市名称
					$info[$k]['am_3_name'] = $name_arr['am_3_name'];//县名称
					$info[$k]['sm_name'] = $name_arr['sm_name'];//学校名称
					$info[$k]['class_name'] = $name_arr['class_name'];//班级名称
					$info[$k]['cm_cate_name'] = $name_arr['cm_cate_name'];//分类名称
					$info[$k]['grade_name'] = $name_arr['grade_name'];//年级名称
					$info[$k]['subject_name'] = $name_arr['subject_name'];//科目名称
					$chl_where=" and cm_id = ".$condition['cm_id'];
					if($condition['ch_id']){
						$chl_where .=" and ch_id = ".$condition['ch_id'];
					}
					if($flag!=6){
						if($flag==1||$flag==2||$flag==3){
							$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE ".$am."='".$v[$id1]."'".$chl_where;	
						}else{
							$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE ".$id1."='".$v[$id1]."'".$chl_where;				
						}					
						$info[$k]['study_user'] = DB::result_first($sql_total_rows);								
					}					
					if($flag==1){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";	
						$info[$k]['am_1_name'] = DB::result_first($sql);							
						$sql ="select count(*) from ".DB::table('trade_course')." tc
								LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id 
								where tc.isdelete = 0 and tc.tc_flag = 2 and ud.isdelete = 0 and ud.am_id_1 = ".$v[$id1]." 
								and tc.cm_id = ".$condition['cm_id'];								
						$info[$k]['all_user'] = DB::result_first($sql);
													
					}elseif ($flag==2){							
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
					
						$info[$k]['am_2_name'] = DB::result_first($sql);
						//查出所有市的记录						
						$sql ="select count(*) from ".DB::table('trade_course')." tc
								LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id
								where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 and ud.am_id_2 = ".$v[$id1]."
								and tc.cm_id = ".$condition['cm_id'];
						$info[$k]['all_user'] = DB::result_first($sql);
												
					}elseif ($flag==3){						
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
					
						$info[$k]['am_3_name'] = DB::result_first($sql);
						//查出所有县区的记录						
						$sql ="select count(*) from ".DB::table('trade_course')." tc
								LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id
								where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 and ud.am_id_3 = ".$v[$id1]."
								and tc.cm_id = ".$condition['cm_id'];
						$info[$k]['all_user'] = DB::result_first($sql);																				
					}elseif($flag == 4){//查学校名称
						$sql = "select sm_name from ".DB::table('school_main')." where sm_id = '".$v[$id1]."' ";
						$info[$k]['sm_name'] = DB::result_first($sql);
						//查出所有学校的记录
						
						$sql ="select count(*) from ".DB::table('trade_course')." tc
									LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id
									where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 and ud.sm_id = ".$v[$id1]."
									and tc.cm_id = ".$condition['cm_id'];
						
						$info[$k]['all_user'] = DB::result_first($sql);
					}elseif($flag == 5){//查班级名称						
						$sql = "select class_name from ".DB::table('class_main')." where class_id = '".$v[$id1]."' ";					
						$info[$k]['class_name'] = DB::result_first($sql);
					
						//查出所有班级的记录
						
						$sql ="select count(DISTINCT(tc.user_id)) from ".DB::table('trade_course')." tc
									LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id
									where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 and ud.class_id = ".$v[$id1]."
									and tc.cm_id = ".$condition['cm_id'];					
						$info[$k]['all_user'] = DB::result_first($sql);
					}elseif($flag == 6){//查学生名称							
						$sql = "SELECT user_realname FROM ".DB::table('user_detail')." where user_id = '".$v[$id1]."' ";							
						$info[$k]['user_name'] = DB::result_first($sql);
						if($condition['ch_id']){
							$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE user_id='".$v[$id1]."' and cm_id =".$condition['cm_id']." and ch_id =".$condition['ch_id'];						
							$is_looked = DB::result_first($sql_total_rows);
							if($is_looked){
								$info[$k]['has_looked'] =1;
							}else{
								$info[$k]['has_looked'] =0;
							}
						}else{
							$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE user_id='".$v[$id1]."' and cm_id =".$condition['cm_id'];
							$info[$k]['looked_hours'] = DB::result_first($sql_total_rows);
							$sql ="select count(*) from ".DB::table('course_hour')." where isdelete = 0 and cm_id = ".$condition['cm_id'];
							$info[$k]['all_hours'] = DB::result_first($sql);							
						}
						
					}
					if($flag==6){
						$info[$k]['class_rate']=sprintf("%.4f",$info[$k]['looked_hours']/$info[$k]['all_hours'])*100;
					}else{
						$info[$k]['class_rate']=sprintf("%.4f",$info[$k]['study_user']/$info[$k]['all_user'])*100;	
					}
				}else{
					unset($info[$k]);
				}
			}
			
		}		
		return $info;
	}
	
	/*
	 *	获取上课详细图信息
	 */
	public function study_detail_info($name_arr=array(),$condition=array()) {
		$info = $res = array();
		$select=$am='';
		$id1=$condition['c_key1'];
		$flag=$condition['flag'];
		$whereSql=$condition['whereSql'];
		if($id1 ){
			if($flag==1){
				$select='ud.am_id_1';
				$am='ue_area_id3';
			}elseif($flag==2){
				$select='ud.am_id_2';
				$am='ue_area_id2';
			}elseif($flag==3){
				$select='ud.am_id_3';
				$am='ue_area_id3';
			}elseif($flag==4){
				$select='ud.sm_id';
			}elseif($flag==5){
				$select='ud.class_id';
			}elseif($flag==6){
				$select='tc.user_id';
			}
			$left = " LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id";
			if($condition['cm']==1){
				$left .= " LEFT JOIN ".DB::table('course_main')." cm ON cm.cm_id=tc.cm_id";
			}
			$sql ="select DISTINCT(".$select.") from ".DB::table('trade_course')." tc ".$left."						
						where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0
						and tc.cm_id = ".$condition['cm_id'].$whereSql;
			$info = DB::fetch_all($sql);
		}
		if($info){			
			foreach ($info as $k => $v){
				if($v[$id1]){					
					$chl_where=" and cm_id = ".$condition['cm_id'];
					if($condition['ch_id']){
						$chl_where .=" and ch_id = ".$condition['ch_id'];
					}
					$res[$k]['key']=$v[$id1];
					if($flag!=6){
						if($flag==1||$flag==2||$flag==3){
							$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE ".$am."='".$v[$id1]."'".$chl_where;
						}else{
							$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE ".$id1."='".$v[$id1]."'".$chl_where;
						}
						$study_user = DB::result_first($sql_total_rows);
					}
					if($flag==1){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
						$res[$k]['name'] = DB::result_first($sql);
						$sql ="select count(*) from ".DB::table('trade_course')." tc
								LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id
								where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 and ud.am_id_1 = ".$v[$id1]."
								and tc.cm_id = ".$condition['cm_id'];
						$all_user = DB::result_first($sql);
							
					}elseif ($flag==2){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
							
						$res[$k]['name'] = DB::result_first($sql);
						//查出所有市的记录
						$sql ="select count(*) from ".DB::table('trade_course')." tc
								LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id
								where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 and ud.am_id_2 = ".$v[$id1]."
								and tc.cm_id = ".$condition['cm_id'];
						$all_user = DB::result_first($sql);
	
					}elseif ($flag==3){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
							
						$res[$k]['name'] = DB::result_first($sql);
						//查出所有县区的记录
						$sql ="select count(*) from ".DB::table('trade_course')." tc
								LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id
								where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 and ud.am_id_3 = ".$v[$id1]."
								and tc.cm_id = ".$condition['cm_id'];
						$all_user = DB::result_first($sql);
					}elseif($flag == 4){//查学校名称
						$sql = "select sm_name from ".DB::table('school_main')." where sm_id = '".$v[$id1]."' ";
						$res[$k]['name'] = DB::result_first($sql);
						//查出所有学校的记录
	
						$sql ="select count(*) from ".DB::table('trade_course')." tc
									LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id
									where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 and ud.sm_id = ".$v[$id1]."
									and tc.cm_id = ".$condition['cm_id'];
	
						$all_user = DB::result_first($sql);
					}elseif($flag == 5){//查班级名称
						$sql = "select class_name from ".DB::table('class_main')." where class_id = '".$v[$id1]."' ";
						$res[$k]['name'] = DB::result_first($sql);
							
						//查出所有班级的记录
													
						$sql ="select COUNT(DISTINCT(tc.user_id)) from ".DB::table('trade_course')." tc
									LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id
									where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 and ud.class_id = ".$v[$id1]."
									and tc.cm_id = ".$condition['cm_id'];
						$all_user = DB::result_first($sql);						
					}elseif($flag == 6){//查学生名称
						$sql = "SELECT user_realname FROM ".DB::table('user_detail')." where user_id = '".$v[$id1]."' ";
						$res[$k]['name'] = DB::result_first($sql);
						if($condition['ch_id']){
							$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE user_id='".$v[$id1]."' and cm_id =".$condition['cm_id']." and ch_id =".$condition['ch_id'];
							$is_looked = DB::result_first($sql_total_rows);
							if($is_looked){
								$res[$k]['has_looked'] =1;
							}else{
								$res[$k]['has_looked'] =0;
							}
						}else{
							$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE user_id='".$v[$id1]."' and cm_id =".$condition['cm_id'];
							$looked_hours = DB::result_first($sql_total_rows);
							$sql ="select count(*) from ".DB::table('course_hour')." where isdelete = 0 and cm_id = ".$condition['cm_id'];
							$all_hours = DB::result_first($sql);
						}
	
					}
					if($flag==6){
						$res[$k]['class_rate']=sprintf("%.4f",$looked_hours/$all_hours)*100;
					}else{
						$res[$k]['class_rate']=sprintf("%.4f",$study_user/$all_user)*100;
					}
				}else{
					unset($info[$k]);
				}
			}
				
		}
		$data['search_data']=$name_arr;
		$data['search_data']['flag']=$flag;
		$data['data']=$res;
		return $data;
	}
	/*
	 *	获取上课率趋势图信息
	 */
	public function study_detail_trend($name_arr=array(),$condition=array()) {
		$info = $res = array();
		$select='';		
		$flag=$condition['flag'];
		$whereSql=$condition['whereSql'];
		$chl_where = $condition['chl_where'];
		$sql ="select  ch_id,ch_name from ".DB::table('course_hour')."
				where isdelete = 0 and cm_id = ".$condition['cm_id'];
		$info = DB::fetch_all($sql);		
		if($info){
			foreach ($info as $k => $v){			
				$res[$k]['key']=$v['ch_id'];
				$res[$k]['name']=$v['ch_name'];
				
				$chl_left = " LEFT JOIN ".DB::table('user_detail')." ud ON chl.user_id=ud.user_id";
				$tc_left = " LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id";
				if($condition['cm']==1){
					$chl_left .= " LEFT JOIN ".DB::table('course_main')." cm ON cm.cm_id=chl.cm_id";
					$tc_left .= " LEFT JOIN ".DB::table('course_main')." cm ON cm.cm_id=tc.cm_id";
				}
				$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." chl ".$chl_left."
						
								  WHERE chl.ch_id ='".$v['ch_id']."'".$chl_where;	
				$study_user = DB::result_first($sql_total_rows);
														
				$sql ="select count(*) from ".DB::table('trade_course')." tc ".	$tc_left."					
						where tc.isdelete = 0 and tc.tc_flag =2  and ud.isdelete = 0 ".$whereSql;					
				$all_user = DB::result_first($sql);									
				$res[$k]['class_rate']=sprintf("%.4f",$study_user/$all_user)*100;									
			}
	
		}
		$data['search_data']=$name_arr;
		$data['search_data']['flag']=$flag;
		$data['data']=$res;
		return $data;
	}
	/*
	 *	获取上课时长信息
	 */
	public function study_time_info($name_arr=array(),$condition=array()) {
		$info = array();
		$id1=$condition['c_key1'];		
		$flag=$condition['flag'];
		$whereSql=$condition['whereSql'];
		$ch_id=$condition['ch_id'];
		if($id1 ){
			if($flag==1){
				$select='ud.am_id_1';
				$am='ue_area_id3';
			}elseif($flag==2){
				$select='ud.am_id_2';
				$am='ue_area_id2';
			}elseif($flag==3){
				$select='ud.am_id_3';
				$am='ue_area_id3';
			}elseif($flag==4){
				$select='ud.sm_id';
			}elseif($flag==5){
				$select='ud.class_id';
			}elseif($flag==6){
				$select='tc.user_id';
			}
			$left = " LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id";
			if($condition['cm']==1){
				$left .= " LEFT JOIN ".DB::table('course_main')." cm ON cm.cm_id=tc.cm_id";
			}
			$sql ="select DISTINCT(".$select.") from ".DB::table('trade_course')." tc ".$left."						
						where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0
						and tc.cm_id = ".$condition['cm_id'].$whereSql;			
			$info = DB::fetch_all($sql);
		}			
		if($info){
			foreach ($info as $k => $v){
				if($v[$id1]){
					$info[$k]['cm_name'] = $name_arr['cm_name'];//课程名称
					$info[$k]['ch_name'] = $name_arr['ch_name'];//课时名称
					$info[$k]['am_1_name'] = $name_arr['am_1_name'];//省名称
					$info[$k]['am_2_name'] = $name_arr['am_2_name'];//市名称
					$info[$k]['am_3_name'] = $name_arr['am_3_name'];//县名称
					$info[$k]['sm_name'] = $name_arr['sm_name'];//学校名称
					$info[$k]['class_name'] = $name_arr['class_name'];//班级名称
					$info[$k]['cm_cate_name'] = $name_arr['cm_cate_name'];//分类名称
					$info[$k]['grade_name'] = $name_arr['grade_name'];//年级名称
					$info[$k]['subject_name'] = $name_arr['subject_name'];//科目名称
					$info[$k]['study_time']='';
					$res_time=$order_num='';
					
					$chl_where=" and cm_id = ".$condition['cm_id'];
					if($ch_id){
						$chl_where .=" and ch_id = ".$condition['ch_id'];
					}
					if($flag==1||$flag==2||$flag==3){
						$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE ".$am."='".$v[$id1]."'".$chl_where;
					}else{
						$sql_total_rows = "SELECT * FROM ".DB::table('course_hour_looked')." WHERE ".$id1."='".$v[$id1]."'".$chl_where;
					}
					
					$times = DB::fetch_all($sql_total_rows);
					if($times){
						foreach ($times as $t_key=>$t_val){
							$res_time +=$t_val['add_time'];
							$order_num +=$t_val['order_num'];
						}						
						$info[$k]['study_time']=sprintf("%.2f",$res_time/60);
						$info[$k]['order_num']=$order_num;
					}					
					if($flag==1){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
						$info[$k]['am_1_name'] = DB::result_first($sql);													
					}elseif ($flag==2){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
							
						$info[$k]['am_2_name'] = DB::result_first($sql);					
					}elseif ($flag==3){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
							
						$info[$k]['am_3_name'] = DB::result_first($sql);						
					}elseif($flag == 4){//查学校名称
						$sql = "select sm_name from ".DB::table('school_main')." where sm_id = '".$v[$id1]."' ";
						$info[$k]['sm_name'] = DB::result_first($sql);						
					}elseif($flag == 5){//查班级名称
						$sql = "select class_name from ".DB::table('class_main')." where class_id = '".$v[$id1]."' ";
						$info[$k]['class_name'] = DB::result_first($sql);							
					}elseif($flag == 6){//查学生名称
						$sql = "SELECT user_realname FROM ".DB::table('user_detail')." where user_id = '".$v[$id1]."' ";							
						$info[$k]['user_name'] = DB::result_first($sql);						
					}					
				}else{
					unset($info[$k]);
				}
			}
				
		}
		return $info;
	}
	/*
	 *	获取上课时长详细图
	 */
	public function study_time_details($name_arr=array(),$condition=array()) {
		$info = $res=array();
		$id1=$condition['c_key1'];		
		$flag=$condition['flag'];
		$whereSql=$condition['whereSql'];
		$ch_id=$condition['ch_id'];
		if($id1 ){
			if($flag==1){
				$select='ud.am_id_1';
				$am='ue_area_id3';
			}elseif($flag==2){
				$select='ud.am_id_2';
				$am='ue_area_id2';
			}elseif($flag==3){
				$select='ud.am_id_3';
				$am='ue_area_id3';
			}elseif($flag==4){
				$select='ud.sm_id';
			}elseif($flag==5){
				$select='ud.class_id';
			}elseif($flag==6){
				$select='tc.user_id';
			}
			$left = " LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id";
			if($condition['cm']==1){
				$left .= " LEFT JOIN ".DB::table('course_main')." cm ON cm.cm_id=tc.cm_id";
			}
			$sql ="select DISTINCT(".$select.") from ".DB::table('trade_course')." tc ".$left."						
						where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0
						and tc.cm_id = ".$condition['cm_id'].$whereSql;
			$info = DB::fetch_all($sql);
		}
		if($info){
			foreach ($info as $k => $v){
				if($v[$id1]){					
					$res[$k]['study_time']='';
					$res[$k]['key']=$v[$id1];
					$res_time='';
						
					$chl_where=" and cm_id = ".$condition['cm_id'];
					if($ch_id){
						$chl_where .=" and ch_id = ".$condition['ch_id'];
					}
					if($flag==1||$flag==2||$flag==3){
						$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_looked')." WHERE ".$am."='".$v[$id1]."'".$chl_where;
					}else{
						$sql_total_rows = "SELECT * FROM ".DB::table('course_hour_looked')." WHERE ".$id1."='".$v[$id1]."'".$chl_where;
					}
						
					$times = DB::fetch_all($sql_total_rows);
					if($times){
						foreach ($times as $t_key=>$t_val){
							$res_time +=$t_val['add_time'];
						}
						$res[$k]['study_time']=floatval(sprintf("%.2f",$res_time/60));
					}
					if($flag==1){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
						$res[$k]['name'] = DB::result_first($sql);
					}elseif ($flag==2){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
							
						$res[$k]['name'] = DB::result_first($sql);
					}elseif ($flag==3){
						$sql = "select am_name from ".DB::table('area_main')." where am_id = '".$v[$id1]."' ";
							
						$res[$k]['name'] = DB::result_first($sql);
					}elseif($flag == 4){//查学校名称
						$sql = "select sm_name from ".DB::table('school_main')." where sm_id = '".$v[$id1]."' ";
						$res[$k]['name'] = DB::result_first($sql);
					}elseif($flag == 5){//查班级名称
						$sql = "select class_name from ".DB::table('class_main')." where class_id = '".$v[$id1]."' ";
						$res[$k]['name'] = DB::result_first($sql);
					}elseif($flag == 6){//查学生名称
						$sql = "SELECT user_realname FROM ".DB::table('user_detail')." where user_id = '".$v[$id1]."' ";
						$res[$k]['name'] = DB::result_first($sql);
					}
				}else{
					unset($info[$k]);
				}
			}
	
		}
		$data['search_data']=$name_arr;
		$data['search_data']['flag']=$flag;
		$data['data']=$res;
		return $data;
	}
	
	/*
	 *	获取上课时长详细图
	 */
	public function study_time_name_details($name_arr,$condition=array()) {
		$info = $res=array();
	
		//$flag=$condition['flag'];
		$whereSql=$condition['whereSql'];
		$ch_id=$condition['ch_id'];
		
		$select='tc.user_id';
		
		$left = " LEFT JOIN ".DB::table('user_detail')." ud ON tc.user_id=ud.user_id";
		
		$sql ="select DISTINCT(".$select.") from ".DB::table('trade_course')." tc ".$left."
					where tc.isdelete = 0 and tc.tc_flag =2 and ud.isdelete = 0 ".$whereSql;
		$info = DB::fetch_all($sql);

		if($info){
			/* $sql = "SELECT * FROM ".DB::table('user_detail')." WHERE isdelete = 0 and class_id ='".$condition['class_id']."' order by user_id";					
			$all_user = DB::fetch_all($sql); */
			foreach ($info as $uk=>$uv){
				$res[$uk]['study_time']=$res[$uk]['order_num']  =$order_num='';
				$sql = "SELECT user_realname FROM ".DB::table('user_detail')." where user_id = '".$uv['user_id']."' ";
				$res[$uk]['name'] = DB::result_first($sql);
				
				foreach ($info as $k => $v){
					if($v['user_id']==$uv['user_id']){						
						$res_time='';								
						if($ch_id){
							$chl_where .=" and ch_id = ".$condition['ch_id'];
						}else{
							$chl_where=" and cm_id = ".$condition['cm_id'];
						}						
						$sql_total_rows = "SELECT * FROM ".DB::table('course_hour_looked')." WHERE user_id ='".$uv['user_id']."'".$chl_where;		
						$times = DB::fetch_all($sql_total_rows);
						if($times){
							foreach ($times as $t_key=>$t_val){
								$res_time +=$t_val['add_time'];						
								$order_num +=$t_val['order_num'];
							}
							$res[$uk]['study_time'] = strval(floatval(sprintf("%.2f",$res_time/60)));
							$res[$uk]['order_num']  = $order_num;
						}																		
					}				
				}
				
			}
	
		}
		$data['name_data']=$name_arr;
		$data['data']=$res;
		return $data;
	}
	/*
	 *	获取上课时长趋势图
	 */
	public function study_time_trend($name_arr=array(),$condition=array()) {
		$info = $res=array();
		$id1=$condition['c_key1'];	
		$flag=$condition['flag'];
		$whereSql=$condition['whereSql'];
		$ch_id=$condition['ch_id'];
	
		$sql ="select  ch_id,ch_name from ".DB::table('course_hour')." 
				where isdelete = 0 and cm_id = ".$condition['cm_id'];
		$info = DB::fetch_all($sql);		
		if($info){
			foreach ($info as $k => $v){	
				$res[$k]['key']=$v['ch_id'];
				$res[$k]['name']=$v['ch_name'];
				$left = " LEFT JOIN ".DB::table('user_detail')." ud ON chl.user_id=ud.user_id";			
				if($condition['cm']==1){
					$left .= " LEFT JOIN ".DB::table('course_main')." cm ON cm.cm_id=chl.cm_id";					
				}
				$sql_total_rows = "SELECT * FROM ".DB::table('course_hour_looked')." chl ".$left."
						
						WHERE chl.ch_id ='".$v['ch_id']."'".$whereSql;		
				$times = DB::fetch_all($sql_total_rows);
				if($times){
					foreach ($times as $t_key=>$t_val){
						$res_time +=$t_val['add_time'];
					}
					$res[$k]['study_time']=floatval(sprintf("%.2f",$res_time/60));
				}else{
					$res[$k]['study_time']="0";
				}																		
			}
	
		}
		$data['search_data']=$name_arr;
		$data['search_data']['flag']=$flag;
		$data['data']=$res;
		return $data;
	}
	/*
	 *	获取上课学生列表信息
	 */
	/* public function student_study_info($cm_id='',$ch_id='',$name_arr=array(),$page_condition=array()) {		
		$page = $page_condition['page'];
		$limit = $page_condition['limit'];
		$perpage = $page_condition['perpage'];
		$start=(($page-1) * $perpage);	
		$wheresql = $page_condition['wheresql'];
		$orderby = $page_condition['orderby'];		
		$info = $page_result=$student_data=array();
									
		$sql = "SELECT user_id,user_realname FROM ".DB::table('user_detail')." 
				WHERE isdelete = 0 ".$wheresql.$page_condition['orderby']." ".DB::limit($start, $limit);		
		$info = DB::fetch_all($sql);
					
		if($info){
			foreach ($info as $k => $v){	
				$student_arr['user_id'] = $v['user_id'];
				$student_arr['user_name'] = $v['user_realname'];
				if($ch_id){
					$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_record')." WHERE user_id='".$v['user_id']."' and cm_id =".$cm_id." and ch_id =".$ch_id;
					$is_looked = DB::result_first($sql_total_rows);
					if($is_looked){
						$student_arr['is_looked'] =1;
					}else{
						$student_arr['is_looked'] =0;
					}
				}else{
					$sql_total_rows = "SELECT count(*) FROM ".DB::table('course_hour_record')." WHERE user_id='".$v['user_id']."' and cm_id =".$cm_id;					
					$student_arr['looked_hours'] = DB::result_first($sql_total_rows); 																								
					$sql ="select count(*) from ".DB::table('course_hour')." where isdelete = 0 and cm_id = ".$cm_id;					
					$student_arr['all_hours'] = DB::result_first($sql);													
					$student_arr['class_rate']=sprintf("%.4f",$student_arr['looked_hours']/$student_arr['all_hours'])*100;								
				}
				$student_arr['cm_name'] = $name_arr['cm_name'];//课程名称
				$student_arr['ch_name'] = $name_arr['ch_name'];//课时名称				
				$student_data[]=$student_arr;										
			}							
		}		
		$sql_total_rows = "select count(*) from ".DB::table('user_detail')." where 1 = 1 ".$whereSql;
		$totalRows = DB::result_first($sql_total_rows);
		$page_result = array(
				'page_now' => $page,
				'total_page' => max(1, ceil($totalRows / $limit)),
				//int 返回结果总数
				'total_rows' => $totalRows,
				//array 一页数据数组
				'page_data' => $student_data
		);
		return $page_result;
	} */
	/**
	 * 获取组合课程详情
	 *
	 * @param $table_name string.
	 * @param $page_condition array.
	 *
	 * @return array
	 */
	public function get_gour_detail($cg_id){
	
		$sql_info = "SELECT cgl.*,cm.* FROM ".DB::table('course_group_lesson')." cgl
				    LEFT JOIN ".DB::table('course_main')." cm ON cgl.cm_id = cm.cm_id
					WHERE cg_id= ".$cg_id." order by cgl_id desc";
		$sql_info_result = DB::fetch_all($sql_info);
		return $sql_info_result;
	}
	/*
	 *  添加或修改子课程
	 */
	public function mod_child($table_name,$cg_id='',$cm_id='') {
		$is_insert = false;	
		$return='';
		if($table_name && $cg_id){
			//先删除原有记录，防止重复添加
			$return = DB::delete($table_name, array('cg_id'=>$cg_id));
			if(!empty($cm_id)){					
				foreach ($cm_id as $key => $val){
					if($val){
						$insert_data=array(
							'cg_id'=>$cg_id,
							'cm_id'=>$val	
						);									
						$is_insert = DB::insert($table_name,$insert_data,true);
					}
				}
				if($is_insert){
					return $is_insert;
				}else{
					return $return;
				}
			}else{
				return $return;
			}
		}		
	}
	/*
	 *	获取课程列表信息
	 */
	public function detail_list($table_name,$page_condition=array()) {
		$page_result = $data_result = array();
		$page = $page_condition['page'];
		$limit = $page_condition['limit'];
		$perpage = $page_condition['perpage'];
		$start=(($page-1) * $perpage);
		$wheresql = $page_condition['wheresql'];
		$orderby = $page_condition['orderby'];
	
		$info = array();
		if($table_name){
			$sql = "SELECT * FROM ".DB::table($table_name)." 
					WHERE 1=1 ".$wheresql." ".$page_condition['orderby']." ".DB::limit($start, $limit);						
			$info = DB::fetch_all($sql);			
		}
		$sql_total_rows = "SELECT count(*) FROM ".DB::table($table_name)." WHERE 1=1 ".$wheresql."";
		$sql_total_rows_result = DB::result_first($sql_total_rows);		
		$page_result = array(
				'page_now'     => $page,
				'total_page'=>max(1, ceil($sql_total_rows_result/$perpage)),
				//int 返回结果总数
				'total_rows' => $sql_total_rows_result,
				//array 一页数据数组
				'page_data' => $info
		);
		return $page_result;
	}
    
	/*
	*  添加信息
	*/
	public function add($table_name,$insert_data=array()) {
		
        $insert_id = '';
        $info= array();
        if($table_name && $insert_data){        	
            $insert_id = DB::insert($table_name,$insert_data,true);
            if($insert_id){
            	$sql = "SELECT * FROM ".DB::table($table_name)."
					WHERE cm_id = ".$insert_id;
            	$info = DB::fetch_first($sql);
            }
        }        
        return $info;
	}
    
	/*
	*  编辑信息
	*/
	public function edit($table_name,$update_data,$where) {		
        //DEBUG 定义返回 消息格式与消息代码编号
        $effect_row='';
        if($update_data && $where){
            $effect_row = DB::update($table_name,$update_data,$where);
        }
        return $effect_row;
	}
    
	/*
	*  删除信息
	*/
	public function delete($table_name,$where) {
        $return=$sql_result='';      
        if($table_name && $where){
        	//判断是否有人购买该课程
        	if($where['cm_id']){
	        	$sql = "SELECT count(*) FROM ".DB::table('trade_course')." WHERE tc_flag = 2  and cm_id = ".$where['cm_id']."";
	        	$sql_result = DB::result_first($sql);
        	}
        	if($sql_result){
        		$return ='-1';
        	}else{
            //$return = DB::delete($table_name, $where, $limit=1);
            //TODO 逻辑删需要创建isdelete字段 0=未删除 1=已删除
            	$return = DB::update($table_name,array('isdelete'=>1,'modify_dateline'=>TIMESTAMP), $where);
        	}
        }
        return $return;
    }
	/*
	*  列表信息
    *
    * @param $table_name string.
    * @param $page_condition array.
    *
    * @return array
	*/
	public function index($table_name,$page_condition){
        $page_result = array();
        $page = $page_condition['page'];
        $limit = $page_condition['limit'];
        $perpage = $page_condition['perpage'];
        $start=(($page-1) * $perpage);
        $wheresql = $page_condition['wheresql'];
        $orderby = $page_condition['orderby'];
        $sql_info = "SELECT * FROM ".DB::table($table_name)." WHERE 1=1 ".$wheresql." ".$page_condition['orderby']." ".DB::limit($start, $limit);
        $sql_info_result = DB::fetch_all($sql_info);
        $sql_total_rows = "SELECT count(*) FROM ".DB::table($table_name)." WHERE 1=1 ".$wheresql."";
        $sql_total_rows_result = DB::result_first($sql_total_rows);
        //DEBUG 列表数据返回结构
        $page_result = array(
            //int 返回结果总数
            'total_rows' => $sql_total_rows_result,
            //array 一页数据数组
            'page_data' => $sql_info_result 
        );
        return $page_result;
	}

   /**
     * 接口 AJAX或外部请求检索数据
     *
     * @param $table_name string.
     * @param $page_condition array.
     *
     * @return array
     */
	public function api($table_name,$page_condition){
        $page_result = array();
        $page = $page_condition['page'];
        $limit = $page_condition['limit'];
        $perpage = $page_condition['perpage'];
        $start=(($page-1) * $perpage);
        $wheresql = $page_condition['wheresql'];
        $orderby = $page_condition['orderby'];
        $sql_info = "SELECT * FROM ".DB::table($table_name)." WHERE 1=1 ".$wheresql." ".$page_condition['orderby']." ".DB::limit($start, $limit);
        $sql_info_result = DB::fetch_all($sql_info);
        $sql_total_rows = "SELECT count(*) FROM ".DB::table($table_name)." WHERE 1=1 ".$wheresql."";
        $sql_total_rows_result = DB::result_first($sql_total_rows);
        //DEBUG 列表数据返回结构
        $page_result = array(
            //int 返回结果总数
            'total_rows' => $sql_total_rows_result,
            //array 一页数据数组
            'page_data' => $sql_info_result 
        );
        return $page_result;
	}

    /*
	*  获取表字段值
    * @param $table_name string
    * @param $key_name string
    * @param $key_id string/array
    * @param $field_name array
    *
    * @return $field_value array
	*/    
    protected static function get_field_value_by_id($table_name,$key_name,$key_id,$field_name) {
		if(is_array($key_id) && !empty($key_id)){
			$key_ids = implode('_',$key_id);
			if(!isset(self::$field_value[$key_ids])) {
				$return = DB::fetch_all('SELECT '.$key_name.','.$field_name.' FROM '.DB::table($table_name)." WHERE ".$key_name." IN (".dimplode($key_id).")");
				foreach($return AS $key => $value){
					$return_tmp[$value[$key_name]] = $value[$field_name];
					unset($return[$key]);
				}
				self::$field_value[$key_ids]=$return_tmp;
				$key_id = $key_ids;
			}else{
				$key_id = $key_ids;
			}
		}else{
			if(!isset(self::$field_value[$key_id])) {
				self::$field_value[$key_id]=DB::result_first('SELECT '.$field_name.' FROM '.DB::table($table_name)." WHERE ".$key_name."='".$key_id."' LIMIT 1");
			}
		}
		return self::$field_value[$key_id];
	}
    
	/*
	* ajax 返回信息
	*/
	public function return_json($error,$message,$output=1) {
		$return_array = array(
			"error" => $error,
			"message" => $message
		);
		if($output){
			echo json_ext($return_array);
			die;
		}else{
			return json_ext($return_array);
		}
	}
	//课程列表和搜索
	public function course_list($table_name,$page_condition){
		$page_result = array();
		$page = $page_condition['page'];
		$limit = $page_condition['limit'];
		$perpage = $page_condition['perpage'];
		$start=(($page-1) * $perpage);
		$wheresql = $page_condition['wheresql'];
		$orderby = $page_condition['orderby'];
		$sql_info = "SELECT * FROM ".DB::table($table_name)." WHERE 1=1 ".$wheresql." ".$page_condition['orderby']." ".DB::limit($start, $limit);
		$sql_info_result = DB::fetch_all($sql_info);
		$sql_total_rows = "SELECT count(*) FROM ".DB::table($table_name)." WHERE 1=1 ".$wheresql."";
		$sql_total_rows_result = DB::result_first($sql_total_rows);
		//DEBUG 列表数据返回结构
		$page_result = array(
				'page_now'=>$page,
				'total_page'=>max(1, ceil($sql_total_rows_result/$perpage)),
				//int 返回结果总数
				'total_rows' => $sql_total_rows_result,
				//array 一页数据数组
				'page_data' => $sql_info_result
		);
		return $page_result;
	}
	
	/*
	 *	根据当前用户获取网校id
	 */
	public function one_so_info($table_name,$key_name,$key_id='') {
		$info = array();
		$so_id='';
		if($table_name && $key_name && $key_id){
			$sql = "SELECT * FROM ".DB::table($table_name)." WHERE ".$key_name."='".$key_id."' LIMIT 1";
			$info = DB::fetch_first($sql);
			$so_id=$info['so_id'];
		}
		return $so_id;
	}
}
?>